'use strict';

const BaseService = require('./base');

class HouseService extends BaseService {
  get _model() {
    return 'House';
  }

  async findOne({ id, status, houseInfoStatus, houseStatus, isShowUser = false, isSimply = false }) {
    const { app } = this;
    const where = this.formatWhere({ exact: { id, status }, inOp: { status: houseStatus } });
    const whereHouseInfo = this.formatWhere({ exact: { status: houseInfoStatus } });
    let include = [];
    if (!isSimply) {
      include = [
        {
          model: app.model.Area,
          attributes: [ 'name' ],
        },
        {
          model: app.model.User,
          as: 'Owner',
          attributes: {
            exclude: app.exclude.concat([ 'password' ]),
          },
          include: [
            {
              model: app.model.UserInfo,
              attributes: {
                exclude: app.exclude,
              },
            },
          ],
        },
        {
          model: app.model.HouseInfo,
          attributes: {
            exclude: app.exclude,
          },
          where: whereHouseInfo,
          required: false,
        },
      ];
      if (isShowUser) {
        include.push({
          model: app.model.Lease,
          attributes: {
            exclude: app.exclude,
          },
          required: false,
          include: [
            {
              model: app.model.User,
              attributes: [ 'nickName' ],
            },
          ],
        });
      }
    }

    return this._findOne({ where, include });
  }

  async create({ name, address, rent, lon, lat, areaId, oid, description, status }) {
    return this._create({
      name, address, rent, lon, lat, areaId, oid, description, status,
    });
  }

  async update({ name, address, rent, lon, lat, areaId, uid, description, views, status, id }) {
    const where = { id };
    const body = this.formatWhere({ exact: { name, address, rent, lon, lat, areaId, uid, description, views, status } });
    return this._update({ body, where });
  }

  async destory(id) {
    const where = { id };
    return this._destory({ where });
  }

  async findList({ name, address, rent, areaId, oid, uid, description, status, houseInfoStatus, houseStatus, pageIndex, pageSize, createdAt, prop, sort, isShowUser = false }) {
    const { app } = this;
    const where = this.formatWhere({
      dim: { name, address, description },
      inOp: { areaId, status: houseStatus },
      range: { createdAt, rent },
      exact: { oid, uid, status },
    });
    const whereHouseInfo = this.formatWhere({ exact: { status: houseInfoStatus } });
    const order = this.formatOrder(prop, sort);
    const include = [
      {
        model: app.model.Area,
        attributes: [ 'name' ],
      },
      {
        model: app.model.User,
        as: 'Owner',
        attributes: [ 'nickName' ],
      },
      {
        model: app.model.HouseInfo,
        attributes: [ 'pictures' ],
        where: whereHouseInfo,
        required: false,
      },
    ];
    if (isShowUser) {
      include.push({
        model: app.model.Lease,
        attributes: [ 'id' ],
        required: false,
        where: {
          status: 2,
        },
        include: [
          {
            model: app.model.User,
            attributes: [ 'nickName' ],
          },
        ],
      });
    }
    return this._findList({ pageIndex, pageSize, where, order, include });
  }

  async findAll({ uid, status, oid, isShowUser = true } = {}) {
    const { app } = this;
    const where = this.formatWhere({ exact: { uid, status, oid } });
    let attributes,
      include = [];
    if (isShowUser) {
      attributes = {
        exclude: app.excludeDate,
      };
      include = [
        {
          model: app.model.HouseInfo,
          attributes: [ 'pictures' ],
        },
      ];
    } else {
      attributes = [ 'id', 'name' ];
    }
    return this._findAll({ where, attributes, include });
  }

  async indexHouseHot() {
    const sql = 'SELECT `house`.`id`, `house`.`name`, `house`.`pictures` AS `houseInfoPictures`,COUNT(*) AS views FROM (SELECT `house`.*,`houseinfo`.`pictures` FROM `house` LEFT JOIN `houseinfo` ON  `house`.`id` = `houseinfo`.`houseId` WHERE `houseinfo`.status = 3) AS `house` LEFT JOIN `collection` on `collection`.`houseId` = `house`.`id` WHERE `house`.`status` in (4,5) AND (`house`.`deletedAt` IS NULL) GROUP BY `house`.`name` ORDER BY `views` DESC LIMIT 10';
    return await this.querySelect(sql);
  }

  async statisticalHouse({ startTime, uid }) {
    if (!startTime) {
      startTime = new Date().getFullYear();
    }
    const sql = 'SELECT `house`.`name`,SUM(`order`.`amount`) AS sum FROM (SELECT * FROM `order` WHERE YEAR(`order`.`startTime`) = ? AND `order`.`status` = 2) AS `order` RIGHT JOIN (SELECT * FROM `house` WHERE `house`.`oid` = ?) as `house` on `order`.`houseId` = `house`.`id` GROUP BY `house`.`name`';
    return await this.querySelect(sql, [ startTime, uid ]);
  }

  async statisticalHouseAll({ startTime }) {
    if (!startTime) {
      startTime = new Date().getFullYear();
    }
    const sql = 'SELECT `house`.`name`,SUM(`order`.`amount`) AS sum FROM (SELECT * FROM `order` WHERE YEAR(`order`.`startTime`) = ? AND `order`.`status` = 2) AS `order` RIGHT JOIN `house` on `order`.`houseId` = `house`.`id` GROUP BY `house`.`name`';
    return await this.querySelect(sql, [ startTime ]);
  }

  async statisticalViews({ uid }) {
    const sql = 'SELECT `house`.`name`,`house`.`views` FROM `house` WHERE `house`.`oid` = ? ORDER BY `house`.`views` DESC LIMIT 5';
    return await this.querySelect(sql, [ uid ]);
  }

  async statisticalViewsAll() {
    const sql = 'SELECT `house`.`name`,`house`.`views` FROM `house` ORDER BY `house`.`views` DESC LIMIT 5';
    return await this.querySelect(sql);
  }

  async statisticalHouseAdd({ startTime, areaId }) {
    if (!startTime) {
      startTime = new Date().getFullYear();
    }
    let where = 'YEAR(`house`.`createdAt`) = ?';
    if (areaId) {
      where = 'YEAR(`house`.`createdAt`) = ? AND FIND_IN_SET(`house`.`areaId`, ?)';
    }
    const sql = `SELECT
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 1 THEN 1 ELSE 0 END) AS "1",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 2 THEN 1 ELSE 0 END) AS "2",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 3 THEN 1 ELSE 0 END) AS "3",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 4 THEN 1 ELSE 0 END) AS "4",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 5 THEN 1 ELSE 0 END) AS "5",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 6 THEN 1 ELSE 0 END) AS "6",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 7 THEN 1 ELSE 0 END) AS "7",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 8 THEN 1 ELSE 0 END) AS "8",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 9 THEN 1 ELSE 0 END) AS "9",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 10 THEN 1 ELSE 0 END) AS "10",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 11 THEN 1 ELSE 0 END) AS "11",
          SUM(CASE WHEN MONTH(\`house\`.\`createdAt\`) = 12 THEN 1 ELSE 0 END) AS "12"
      FROM (SELECT \`house\`.\`createdAt\` FROM \`house\` WHERE ${where}) AS \`house\``;
    return await this.querySelect(sql, [ startTime, areaId ]);
  }

  async indexLike({ firstRent = [ 0, 100000 ], secondRent = [ 0, 100000 ], firstArea = '', secondArea = '', pageSize = 20 }) {
    const sql = `
        SELECT \`house\`.\`id\`,\`house\`.\`name\`,\`house\`.\`areaId\`,\`house\`.\`rent\`,\`house\`.\`pictures\` FROM (SELECT  \`house\`.\`id\`,\`house\`.\`name\`,\`house\`.\`areaId\`,\`house\`.\`rent\`,\`houseinfo\`.\`pictures\` FROM \`house\` LEFT JOIN \`houseinfo\` ON \`house\`.\`id\` = \`houseinfo\`.\`houseId\` WHERE \`house\`.\`status\` in (4,5) AND (\`house\`.\`deletedAt\` IS NULL) ORDER BY RAND() LIMIT ?) AS \`house\` ORDER BY
            (CASE WHEN \`house\`.\`rent\` BETWEEN ? AND ? THEN 1 ELSE 0 END) DESC,
            (CASE WHEN \`house\`.\`areaId\` = ? THEN 1 ELSE 0 END) DESC,
            (CASE WHEN \`house\`.\`rent\` BETWEEN ? AND ? THEN 2 ELSE 0 END) DESC,
            (CASE WHEN \`house\`.\`areaId\` = ? THEN 2 ELSE 0 END) DESC
            LIMIT ?
    `;

    return await this.querySelect(sql, [ pageSize, firstRent[0], firstRent[1], firstArea, secondRent[0], secondRent[1], secondArea, Math.floor(pageSize / 2) ]);
  }
}

module.exports = HouseService;
